package ru.spies.dbwork;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.List;
import java.util.Properties;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.servlet.http.HttpServletRequest;

import ru.spies.dbfunctional.ICallFunction;
import ru.spies.entities.ITableClass;
import ru.spies.help.DataTableCreator;
import ru.spies.help.MyLogger;
import ru.spies.requests.SPRequestWork;

public class DBWork {

	private static final String PERSISTENCE_UNIT_NAME = "kyrsach";
	private static final String USERNAME = "spy";
	private static final String DRIVERNAME = "org.postgresql.Driver";
	private static final String CONNECTION = "jdbc:postgresql://localhost:5432/spies";
	private static final String OPENJPA_CONNECTION_URL = "openjpa.ConnectionURL";
	private static final String OPENJPA_CONNECTION_DRIVER_NAME = "openjpa.ConnectionDriverName";
	private static final String OPENJPA_CONNECTION_USER_NAME = "openjpa.ConnectionUserName";
	private static final String PASSWORD = "123";
	private static final String OPENJPA_CONNECTION_PASSWORD = "openjpa.ConnectionPassword";
	private static final String OPENJPA_JDBC_SYNCHRONIZE_MAPPINGS = "openjpa.jdbc.SynchronizeMappings";
	private static final String JDBC_SYNCHRONIZE_MAPPINGS = "buildSchema";
	
	private static final String TABLE_NAME="callSP";
	private static final String TOP_AGENTS_COLUMNS[]={
		"recruiter","department","name",
		"count_recruits","status","tasks_completed"
		};
	private static final String NORMAL_TOP_AGENTS_COLUMNS[]={
		"Вербовщик","Отдел", "Имя агента",
		"Завербовал агентов", "Раскрыт?", "Заданий выполнено"
		};	
	private static final String POPULAR_DEPARTMENT[]={
		"name","country","resident",
		"description","creationDate"
		};
	private static final String NORMAL_POPULAR_DEPARTMENT[]={
		"Отдел","Страна","Глава отдела",
		"Описание","Дата создания отдела"
		};
	private static final String DOCUMENT_BY_IMPORTANCE[]={
		"task","importance","agent","text_link"
		};
	private static final String NORMAL_DOCUMENT_BY_IMPORTANCE[]={
		"Задание","Тип важности","Отправитель","Ссылка"
		};
	
	private static DBWork statThis=null;
	private EntityManagerFactory emf=null;
	
	
	private DBWork() {
		Properties props = new Properties();
		props.put(OPENJPA_CONNECTION_URL, CONNECTION);
		props.put(OPENJPA_CONNECTION_DRIVER_NAME,DRIVERNAME);
		props.put(OPENJPA_CONNECTION_USER_NAME,USERNAME);
		props.put(OPENJPA_CONNECTION_PASSWORD,PASSWORD);
		props.put(OPENJPA_JDBC_SYNCHRONIZE_MAPPINGS,JDBC_SYNCHRONIZE_MAPPINGS);
		emf = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME, props);
	}
	
	public static DBWork getInstance(){
		if (statThis==null){
			statThis= new DBWork();
		}	
		return statThis;
	}
	

	public Collection<?> getTableObjects(String queryString){
		List<?> list=null;
		EntityManager em;
		em = emf.createEntityManager();
		em.getTransaction().begin();
		
		try {
			Query query = em.createQuery(queryString);
			list = query.getResultList();
			em.getTransaction().commit();
			MyLogger.getInstance().getLog().info(MyLogger.INFO_WAS_READ_WITHOUT_ANY_ERRORS);
		} catch (Exception e) {
			MyLogger.getInstance().getLog().error(MyLogger.EXCEPTION,e);
			em.getTransaction().rollback();
		} finally {
			em.close();
		}
		return  list;
	}
			
	public ITableClass findObjectFromTable(ITableClass obj){
		if (obj.getParamForFindQuery()==null) return null;
		EntityManager em = emf.createEntityManager();
		em.getTransaction().begin();
		ITableClass retObj=obj.createThisObject();
		try {
			Query q = em.createQuery(obj.getQueryForFind());
			retObj=(ITableClass) q.setParameter("param", wrapForNeededType(obj.getParamForFindQuery())).getSingleResult();
			MyLogger.getInstance().getLog().info(MyLogger.INFO_WAS_READ_WITHOUT_ANY_ERRORS);
			em.getTransaction().commit();
		} catch (Exception e){
			MyLogger.getInstance().getLog().error(MyLogger.EXCEPTION,e);
			em.getTransaction().rollback();
		} finally{
			em.close();
		}
		return retObj;
	}
	
	public void updateTableObject(Object tableObject){
		EntityManager em = emf.createEntityManager();
		em.getTransaction().begin();
		try {
			em.merge(tableObject);
			em.getTransaction().commit();
			MyLogger.getInstance().getLog().info(MyLogger.INFO_WAS_WROTE_WITHOUT_ANY_ERRORS);
		} catch (Exception e) {
			MyLogger.getInstance().getLog().error(MyLogger.EXCEPTION,e);
			em.getTransaction().rollback();
		} finally {
			em.close();
		}
	}
	
	public void addPersistTableObject (Object TableObject){
		EntityManager em = emf.createEntityManager();
		em.getTransaction().begin();
		try {
			em.persist(TableObject);
			em.getTransaction().commit();
			MyLogger.getInstance().getLog().info(MyLogger.INFO_WAS_WROTE_WITHOUT_ANY_ERRORS);
		} catch (Exception e) {
			MyLogger.getInstance().getLog().error(MyLogger.EXCEPTION,e);
			em.getTransaction().rollback();
		} finally {
			em.close();
		}
	}
	
	public void removeObjectFromTable(ITableClass tableObject){
		EntityManager em = emf.createEntityManager();
		em.getTransaction().begin();
		try {
			em.remove(em.find(tableObject.getClass(), tableObject.getId().intValue()));
			em.getTransaction().commit();
			MyLogger.getInstance().getLog().info(MyLogger.INFO_WAS_WROTE_WITHOUT_ANY_ERRORS);
		} catch (Exception e) {
			MyLogger.getInstance().getLog().error(MyLogger.EXCEPTION,e);
			em.getTransaction().rollback();
		} finally {
			em.close();
		}
	}
	
	private Object wrapForNeededType(Object whatToWrap){
		Object retVal;
		try {
			retVal=Integer.parseInt(whatToWrap.toString());
		} catch (Exception e) {
			retVal=whatToWrap;
		}
		return retVal;
	}
	
	private CallableStatement getCallableStatement(Connection connection,String storedProcedure) throws SQLException{
		return connection.prepareCall(storedProcedure);
	}
	
	public Connection getConnection() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException{
		Class.forName(DRIVERNAME).newInstance();
		return DriverManager.getConnection(CONNECTION, USERNAME, PASSWORD);
	}
	
	private void closeConnection(Connection connection) throws SQLException{
		connection.close();
	}
	
	public ICallFunction getTopAgents(){
		return new ICallFunction() {
			
			private Connection con;
			private CallableStatement statement;
			
			@Override
			public ResultSet getResultSet(HttpServletRequest request) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
				String storedProcedure = "{call getTopAgents( ? ) }";
				statement = getCallableStatement(con,storedProcedure);
				String notParsedCount = SPRequestWork.getInstance().getRequestParam(request);
				int count;
				if (notParsedCount==null){
					count = 1;
				} else {
					count = Integer.valueOf(notParsedCount);
					if (count<1){
						count = 1;
					}
				}
				statement.setInt(1, count);
				statement.execute();
				return statement.getResultSet();
			}
			
			@Override
			public String[] getColumns(boolean withLocale) {
				if (withLocale){
					return NORMAL_TOP_AGENTS_COLUMNS;
				}
				return TOP_AGENTS_COLUMNS;
			}
			
			@Override
			public void fillTable(ResultSet result, String[] cols) throws SQLException {
				StringBuffer dataTableContent = new StringBuffer();
				DataTableCreator creator = DataTableCreator.getInstance(TABLE_NAME);
				while (result.next()){
					String vals[] = new String [6];
					for (int i = 0; i < vals.length; i++) {
						if (i==4){
							vals[i]=convertAgentStatus(result.getBoolean(i+2));
						} else {
							vals[i]=result.getString(i+2);
						}		
					}
					dataTableContent.append(creator.getTableRow(cols, vals));
				}
				creator.wrapTable(dataTableContent.toString());
				result.close();
			}

			@Override
			public void openConnection() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
				con = getConnection();
			}

			@Override
			public void closeConnection() throws SQLException {
				statement.close();
				DBWork.this.closeConnection(con);
			}
			
			private String convertAgentStatus(boolean value){
				if (value){
					return "Раскрыт";
				} else {
					return "Не раскрыт";
				}
			}
			
		};
	}
	
	public ICallFunction getPopularDepartment(){
		return new ICallFunction() {
			
			private Connection con;
			private CallableStatement statement;
			
			@Override
			public void openConnection() throws InstantiationException,
					IllegalAccessException, ClassNotFoundException, SQLException {
				con = getConnection();			
			}
			
			@Override
			public ResultSet getResultSet(HttpServletRequest request)
					throws SQLException, InstantiationException,
					IllegalAccessException, ClassNotFoundException {
				String storedProcedure = "{ call getPopularDepartment() }";
				statement = getCallableStatement(con,storedProcedure);
				statement.execute();
				return statement.getResultSet();
			}
			
			@Override
			public String[] getColumns(boolean withLocale) {
				if (withLocale){
					return NORMAL_POPULAR_DEPARTMENT;
				}
				return POPULAR_DEPARTMENT;
			}
			
			@Override
			public void fillTable(ResultSet result, String[] cols) throws SQLException {
				StringBuffer dataTableContent = new StringBuffer();
				DataTableCreator creator = DataTableCreator.getInstance(TABLE_NAME);
				SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
				while (result.next()){
					String vals[] = new String [5];
					for (int i = 0; i < vals.length-1; i++) {
							vals[i]=result.getString(i+2);
					}
					vals[vals.length-1]=sdf.format(result.getDate(vals.length+1));
					dataTableContent.append(creator.getTableRow(cols, vals));
				}
				creator.wrapTable(dataTableContent.toString());
				result.close();
			}
			
			@Override
			public void closeConnection() throws SQLException {
				statement.close();
				DBWork.this.closeConnection(con);
			}
		};
	}
	
	public ICallFunction getImportantDocuments(){
		return new ICallFunction() {
			
			private Connection con;
			private CallableStatement statement;
			
			@Override
			public void openConnection() throws InstantiationException,
					IllegalAccessException, ClassNotFoundException, SQLException {
				con = getConnection();	
			}
			
			@Override
			public ResultSet getResultSet(HttpServletRequest request)
					throws SQLException, InstantiationException,
					IllegalAccessException, ClassNotFoundException {
				String storedProcedure = "{call getDocumentsByImportance( ? ) }";
				statement = getCallableStatement(con,storedProcedure);
				String notParsedIdImportance = SPRequestWork.getInstance().getRequestParam(request);
				if (notParsedIdImportance==null){
					return null;
				}
				int idImportance = Integer.valueOf(notParsedIdImportance);
				statement.setInt(1, idImportance);
				statement.execute();
				return statement.getResultSet();
			}
			
			@Override
			public String[] getColumns(boolean withLocale) {
				if (withLocale){
					return NORMAL_DOCUMENT_BY_IMPORTANCE;
				}
				return DOCUMENT_BY_IMPORTANCE;
			}
			
			@Override
			public void fillTable(ResultSet result, String[] cols) throws SQLException {
				StringBuffer dataTableContent = new StringBuffer();
				DataTableCreator creator = DataTableCreator.getInstance(TABLE_NAME);
				while (result.next()){
					String vals[] = new String [4];
					for (int i = 0; i < vals.length; i++) {
							vals[i]=result.getString(i+2);
					}
					dataTableContent.append(creator.getTableRow(cols, vals));
				}
				creator.wrapTable(dataTableContent.toString());
				result.close();
			}
			
			@Override
			public void closeConnection() throws SQLException {
				statement.close();
				DBWork.this.closeConnection(con);
			}
		};
	}
				
}
